/*
 * @Author: zhangyang
 * @Date: 2024-05-29 14:09:37
 * @LastEditTime: 2024-06-03 17:18:28
 * @Description: 
 * @LastEditors: Please set LastEditors
 * ██████╗ ██╗     ██╗   ██╗███████╗███████╗██╗   ██╗ ██████╗ ██╗   ██╗███╗   ██╗ ██████╗               ██╗    ██╗███████╗██████╗ 
 * ██╔══██╗██║     ██║   ██║██╔════╝██╔════╝╚██╗ ██╔╝██╔═══██╗██║   ██║████╗  ██║██╔════╝               ██║    ██║██╔════╝██╔══██╗
 * ██████╔╝██║     ██║   ██║█████╗  ███████╗ ╚████╔╝ ██║   ██║██║   ██║██╔██╗ ██║██║  ███╗    █████╗    ██║ █╗ ██║█████╗  ██████╔╝
 * ██╔══██╗██║     ██║   ██║██╔══╝  ╚════██║  ╚██╔╝  ██║   ██║██║   ██║██║╚██╗██║██║   ██║    ╚════╝    ██║███╗██║██╔══╝  ██╔══██╗
 * ██████╔╝███████╗╚██████╔╝███████╗███████║   ██║   ╚██████╔╝╚██████╔╝██║ ╚████║╚██████╔╝              ╚███╔███╔╝███████╗██████╔╝
 * ╚═════╝ ╚══════╝ ╚═════╝ ╚══════╝╚══════╝   ╚═╝    ╚═════╝  ╚═════╝ ╚═╝  ╚═══╝ ╚═════╝                ╚══╝╚══╝ ╚══════╝╚═════╝
 * Copyright (c) 2024 to current by BluesYoung-web, All Rights Reserved. 
 */
import 'dart:io';

import 'package:flutter/material.dart';
import 'package:sqflite/sqflite.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart' as sqflite_polyfill;

const sqlSpliter = '---;;---';

/// 初始化数据库
/// ! android 一次只能创建一张表
/// ! 使用 ---;;--- 进行分割
const initSQL = '''
/* 账单表 */
CREATE TABLE IF NOT EXISTS "${DataBase.tableBill}" (
	"id" INTEGER NOT NULL UNIQUE,
	-- 金额（分）
	"amount" INTEGER NOT NULL,
	-- 分类 id
	"categrayId" INTEGER NOT NULL,
	-- 账单时间
	"time" TEXT NOT NULL,
	-- 账单备注/描述
	"remark" TEXT NOT NULL,
	-- 创建时间
	"createdTime" TEXT NOT NULL,
	-- 更新时间
	"updatedTime" TEXT,
	PRIMARY KEY("id")	
);
$sqlSpliter
/* 分类表 */
CREATE TABLE IF NOT EXISTS "${DataBase.tableCategray}" (
	"autoid" INTEGER NOT NULL UNIQUE,
	-- 分类名称
	"name" TEXT NOT NULL,
	-- 分类图标
	"icon" TEXT,
	-- 收入/支出
	"type" TEXT CHECK("type" in ('income', 'outcome')) NOT NULL,
	PRIMARY KEY("autoid"),
	FOREIGN KEY ("autoid") REFERENCES "${DataBase.tableBill}"("categrayId")
	ON UPDATE NO ACTION ON DELETE NO ACTION
);
''';

class DataBase {
  /// sqllite 数据库名称
  static const String dbName = 'data.db';

  /// 分类表
  static const String tableCategray = 'categories';

  /// 账单表
  static const String tableBill = 'bills';

  static Future<Database> openDB() async {
    // 判断当前是否为 windows
    if (Platform.isWindows) {
      sqflite_polyfill.sqfliteFfiInit();
      databaseFactory = sqflite_polyfill.databaseFactoryFfi;
      debugPrint('sqflite_polyfill.sqfliteFfiInit');
    }

    debugPrint('data base path: ${await getDatabasesPath()}');

    var db =
        await openDatabase(dbName, version: 1, onCreate: (db, version) async {
      debugPrint('start create table');
      debugPrint('sql: $initSQL');

      List<String> sqls = initSQL.split(sqlSpliter);

      await db.transaction((txn) async {
        for (var sql in sqls) {
          await txn.execute(sql);
        }
      });

      debugPrint('end create table');
    });

    return db;
  }
}
